package com.ovolucky.mapmytrip.app.storage;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.google.android.gms.maps.model.BitmapDescriptorFactory;
import com.google.android.gms.maps.model.LatLng;
import com.google.android.gms.maps.model.MarkerOptions;

import java.util.ArrayList;
import java.util.List;

import static com.ovolucky.mapmytrip.app.storage.CommonConstants.DATABASE_NAME;
import static com.ovolucky.mapmytrip.app.storage.CommonConstants.DATABASE_VERSION;

/**
 * Created by ovo on 6/26/14.
 */
public class TripStore extends SQLiteOpenHelper {

  // Trips table
  private static final String TRIPS_TABLE_NAME = "trips";
  private static final String TRIP_ID_COLUMN_NAME = "trip_id";
  private static final String TITLE_COLUMN_NAME = "title";
  private static final String SUMMARY_COLUMN_NAME = "summary";
  private static final String START_TIME_COLUMN_NAME = "start_time";
  private static final String END_TIME_COLUMN_NAME = "end_time";
  private static final String TRIPS_TABLE_CREATE =
      "CREATE TABLE " + TRIPS_TABLE_NAME + " (" +
          TRIP_ID_COLUMN_NAME + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
          TITLE_COLUMN_NAME + " TEXT, " +
          SUMMARY_COLUMN_NAME + " TEXT, " +
          START_TIME_COLUMN_NAME + " INTEGER, " +
          END_TIME_COLUMN_NAME + " INTEGER" +
          ");";

  // Markers table
  private static final String MARKERS_TABLE_NAME = "markers";
  private static final String MARKER_TRIP_ID_COLUMN_NAME = "trip_id";
  private static final String LATITUDE_COLUMN_NAME = "latitude";
  private static final String LONGITUDE_COLUMN_NAME = "longitude";
  private static final String MARKER_TITLE_COLUMN_NAME = "title";
  private static final String DESCRIPTION_COLUMN_NAME = "description";
  private static final String MARKERS_TABLE_CREATE =
      "CREATE TABLE " + MARKERS_TABLE_NAME + " (" +
          MARKER_TRIP_ID_COLUMN_NAME + " INTEGER, " +
          LATITUDE_COLUMN_NAME + " REAL, " +
          LONGITUDE_COLUMN_NAME + " REAL, " +
          MARKER_TITLE_COLUMN_NAME + " TEXT, " +
          DESCRIPTION_COLUMN_NAME + " TEXT" + ");";

  public TripStore(Context context) {
    super(context, DATABASE_NAME, null, DATABASE_VERSION);
  }

  @Override
  public void onCreate(SQLiteDatabase database) {
    database.execSQL(TRIPS_TABLE_CREATE);
    database.execSQL(MARKERS_TABLE_CREATE);
  }

  @Override
  public void onUpgrade(
      SQLiteDatabase database, int oldVersion, int newVersion) {
    database.execSQL("DROP TABLE IF EXISTS " + getDatabaseName());
    onCreate(database);
  }

  // TODO(ovo): Do better than returning null.
  public TripMetadata getTrip(long tripId) {
    String selectQuery =
        String.format("SELECT %s, %s, %s, %s, %s FROM %s WHERE %s = '%d'",
            TRIP_ID_COLUMN_NAME,
            TITLE_COLUMN_NAME,
            SUMMARY_COLUMN_NAME,
            START_TIME_COLUMN_NAME,
            END_TIME_COLUMN_NAME,
            TRIPS_TABLE_NAME,
            TRIP_ID_COLUMN_NAME,
            tripId);
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);
    return cursor.moveToNext() ? new TripMetadata(
        cursor.getLong(0), cursor.getString(1), cursor.getString(2),
        cursor.getLong(3), cursor.getLong(4)) : null;
  }

  public List<TripMetadata> listAllTrips() {
    List<TripMetadata> result = new ArrayList<>();
    String selectQuery =
        String.format("SELECT %s, %s, %s, %s, %s FROM %s",
            TRIP_ID_COLUMN_NAME,
            TITLE_COLUMN_NAME,
            SUMMARY_COLUMN_NAME,
            START_TIME_COLUMN_NAME,
            END_TIME_COLUMN_NAME,
            TRIPS_TABLE_NAME);
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);

    if (cursor.moveToFirst()) {
      do {
        TripMetadata trip = new TripMetadata(
            cursor.getLong(0), cursor.getString(1), cursor.getString(2),
            cursor.getLong(3), cursor.getLong(4));
        result.add(trip);
      } while (cursor.moveToNext());
    }
    cursor.close();
    db.close();
    return result;
  }

  public long saveTrip(String title, String summary) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(TITLE_COLUMN_NAME, title);
    values.put(SUMMARY_COLUMN_NAME, summary);
    values.put(START_TIME_COLUMN_NAME, 0);
    values.put(END_TIME_COLUMN_NAME, 0);

    long tripId = db.insert(TRIPS_TABLE_NAME, null, values);
    db.close();
    return tripId;
  }

  public void insertMarker(long tripId, LatLng latLng, String title, String description) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(MARKER_TRIP_ID_COLUMN_NAME, tripId);
    values.put(LATITUDE_COLUMN_NAME, latLng.latitude);
    values.put(LONGITUDE_COLUMN_NAME, latLng.longitude);
    values.put(MARKER_TITLE_COLUMN_NAME, title);
    values.put(DESCRIPTION_COLUMN_NAME, description);

    db.insert(MARKERS_TABLE_NAME, null, values);
    db.close();
  }

  public List<MarkerOptions> getAllMarkers(long tripId) {
    List<MarkerOptions> result = new ArrayList<>();
    String selectQuery =
        String.format("SELECT %s, %s, %s, %s FROM %s WHERE %s = '%d'",
            LATITUDE_COLUMN_NAME,
            LONGITUDE_COLUMN_NAME,
            MARKER_TITLE_COLUMN_NAME,
            DESCRIPTION_COLUMN_NAME,
            MARKERS_TABLE_NAME,
            MARKER_TRIP_ID_COLUMN_NAME,
            tripId);
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(selectQuery, null);

    if (cursor.moveToFirst()) {
      do {
        LatLng latLng = new LatLng(Double.parseDouble(cursor.getString(0)),
            Double.parseDouble(cursor.getString(1)));
        MarkerOptions markerOptions = new MarkerOptions()
            .position(latLng)
            .title(cursor.getString(2))
            .snippet(cursor.getString(3))
            .draggable(true)
            .icon(BitmapDescriptorFactory.defaultMarker(
                BitmapDescriptorFactory.HUE_GREEN))
            .alpha(0.6f);

        result.add(markerOptions);
      } while (cursor.moveToNext());
    }
    cursor.close();
    db.close();
    return result;
  }

  public static class TripMetadata {
    private final long tripId;
    private final String title;
    private final String summary;
    private final long startTimestamp;
    private final long endTimestamp;

    private TripMetadata(
        long tripId, String title, String summary, long startTimestamp, long endTimestamp) {
      this.tripId = tripId;
      this.title = title;
      this.summary = summary;
      this.startTimestamp = startTimestamp;
      this.endTimestamp = endTimestamp;
    }

    public String getTitle() {
      return title;
    }

    public String getSummary() {
      return summary;
    }

    public long getTripId() {
      return tripId;
    }

    @Override
    public String toString() {
      return getTitle();
    }
  }
}
